package com.qf.blog.dao.daoimpl;

import com.qf.blog.core.utils.DruidTool2;
import com.qf.blog.dao.SystemUserDAO;
import com.qf.blog.entity.SystemUserEntity;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.util.Date;
import java.util.List;

/**
 * @author zyt
 * @version 1.0
 */
public class SystemUserDAOImpl implements SystemUserDAO {
    // 先创建QueryRunner对象
    QueryRunner qr = new QueryRunner(DruidTool2.dataSource);
    /**
     * 插入的方法
     */
    @Override
    public Integer insert(SystemUserEntity systemUserEntity) throws Exception {
        // 1. 先准备sql语句
        systemUserEntity.setCreateTime(new Date());  // 把注册时间设置为当前时间
        String sql = "insert into t_system_user (username,password,sex,email,createTime,birthday) value(?,?,?,?,?,?)";
        return qr.update(sql, systemUserEntity.getUsername(), systemUserEntity.getPassword(), systemUserEntity.getSex(), systemUserEntity.getEmail(), systemUserEntity.getCreateTime(), systemUserEntity.getBirthday());
    }

    /**
     * 根据id查询
     *```````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public SystemUserEntity selectByid(Integer id) throws Exception {
        // 1.先准备sql语句
        String sql = "select * from t_system_user where id =?";
        return qr.query(sql, new BeanHandler<SystemUserEntity>(SystemUserEntity.class));
    }

    /**
     * 根据id修改
     *
     * @param systemUserEntity
     * @return
     * @throws Exception
     */
    @Override
    public Integer updeteByid(SystemUserEntity systemUserEntity) throws Exception {
        // 1. 先准备sql语句
        String sql = "update t_system_user set username = ?,password = ?,sex = ?,email = ?,birthday = ? where id = ?";
        return qr.update(sql, systemUserEntity.getUsername(), systemUserEntity.getPassword(), systemUserEntity.getSex(), systemUserEntity.getEmail(), systemUserEntity.getBirthday(), systemUserEntity.getId());
    }


    /**
     * 根据id删除
     *
     * @param id
     * @return
     * @throws Exception
     */
    @Override

    public Integer deleteByid(Integer id) throws Exception {
        // 1. 先准备sql语句
        String sql = "update t_system_user set status = ? where id = ? and status = 0";
        return qr.update(sql, 1, id);
    }

    /**
     * 查询所有
     *
     * @return
     * @throws Exception
     */
    @Override
    public List<SystemUserEntity> selectList() throws Exception {
        // 先准备sql语句
        String sql = "select * from t_system_user where status=0";
        return qr.query(sql, new BeanListHandler<SystemUserEntity>(SystemUserEntity.class));
    }

    /**
     * 多选删除
     * @param ids
     * @return
     * @throws Exception
     */
    @Override
    public Integer batchRemove(List<Integer> ids) throws Exception {
        // 创建一个 可变的字符串
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("update t_system_user set STATUS=1 where id in(");
        // 遍历这个集合
        for (int i = 0; i < ids.size(); i++) {
            // if判断这个集合是否遍历结束
            if (i != ids.size() - 1) {
                // 不是最后一个 就拼接“ ？，”   ？是占位符
                stringBuilder.append("?,");
            } else {
                // 是最后一个 就不要，了
                stringBuilder.append("?");
            }
        }
        stringBuilder.append(") and status=0");  // 这个是拼接 id（？,？,？）占位符的数量
        // 把接收的数据输出在控制台
        System.out.println("接收的批量删除的(sql语句)为+"+stringBuilder.toString());
        // 返回受影响的行数
        return  qr.update(stringBuilder.toString(),ids.toArray());
    }
//    public static void main(String[] args) {
//        StringBuilder stringBuilder=new StringBuilder();
//        StringBuilder updjfkld = stringBuilder.append("updjfkld");
//        System.out.println("builder"+updjfkld);
//        System.out.println("toString:"+updjfkld.toString());
//    }
}
